<?php
/*	------------------------   Musiker Data Loading Script   -------------------------
		This script replicates the iTunes Library data within a MySQL database for
		access by Musiker.  The script is designed to be run from the command line
		since large iTunes libraries can take a long time to run, causing it to 
		timeout if you attempted to run it from within a web browser.
		
		You will need to run this script any time you have made changes within iTunes
		that you want Musiker to show.
		
		How to use this script:
		1)	Make sure that the $ITUNES_LIBRARY_XML_PATH variable is set in the 
			local_config.php file.  Remember that Apache and PHP need to have read 
			permissions on the xml file.
		1b)	If you are running in debug mode, verify the $DEBUG_FILE variable below
			and create the debug file in advance, giving Apache write permissions on it.
		2)	From the terminal, cd to the musiker folder.  Run the following command:
				php -f iTunesLib_Loader.php
				
		If you encounter any errors, please report them to me either via GoogleCode,
		or directly via email at musiker@chipersoft.com  Please include a transcript 
		of the script output, and if possible the debug mode output file.

*/

$debug = false; //Set to true to output all queries to a debug file instead of to the database;
$DEBUG_FILE = 'itunesDB.sql';

if (file_exists('local_config.php')) include_once('local_config.php');
else include_once('local_config.default.php');


/*--------INITIAL FUNCTIONS--------------------------------------------------------*/

include('libs/libSimpleMySQL.php');

if (!($DATABASE_ADDRESS && $DATABASE_LOGIN && $DATABASE_PASSWORD && $DATABASE_DBNAME)) die "STOP: Database access information is missing from the configuration file.";
if (!$ITUNES_LIBRARY_XML_PATH) die "STOP: iTunes Library XML file path is not set.";
if (!file_exists($ITUNES_LIBRARY_XML_PATH)) die "STOP: Defined iTunes Library XML file does not exist.";



function parseValue( $valueNode, $depth = -1) {
  $valueType = $valueNode->nodeName;

  $transformerName = "parse_$valueType";

  if ( is_callable($transformerName)) {
    // there is a transformer function for this node type
    return call_user_func($transformerName, $valueNode, $depth);
  }

  // if no transformer was found
  return null;
}

	function parse_integer( $integerNode ) {
		return $integerNode->textContent;
	}

	function parse_string( $stringNode ) {
		return $stringNode->textContent;
	}

	function parse_date( $dateNode ) {
		return $dateNode->textContent;
	}

	function parse_true( $trueNode ) {
		return true;
	}

	function parse_false( $trueNode ) {
		return false;
	}
	
	function parse_data( $dataNode ) {
		return $dataNode->textContent;
	}
	
	function parse_dict( $dictNode, $depth = -1 ) {
		if ($depth==0) return $dictNode;//"DICT BELOW LEVEL";
		
		$dict = array();

		// for each child of this node
		for (
			$node = $dictNode->firstChild;
		$node != null;
		$node = $node->nextSibling
		) {
			if ( $node->nodeName == "key" ) {
				$key = $node->textContent;

				$valueNode = $node->nextSibling;

				// skip text nodes
				while ( $valueNode->nodeType == XML_TEXT_NODE ) {
					$valueNode = $valueNode->nextSibling;
				}


				// recursively parse the children
				$value = parseValue($valueNode, $depth-1);

				$dict[$key] = $value;

				//if ($value instanceof DOMElement) $value = "[XML Node: ".$value->childNodes->length."children]";
				//print "$key -> $value\n";
			}
		}

		return $dict;
	}

	function parse_array( $arrayNode, $depth = -1 ) {
		if ($depth==0) return $arrayNode;//"ARRAY BELOW LEVEL";
		$array = array();

		for (
			$node = $arrayNode->firstChild;
		$node != null;
		$node = $node->nextSibling
		) {
			if ( $node->nodeType == XML_ELEMENT_NODE && $depth!=0) {
				
				array_push($array, parseValue($node, $depth-1));
				
			}
		}

		return $array;
	}

function iTunesDateTOMySQLDate ($date) {
	return str_replace('T',' ',str_replace('Z','',$date));
}

	
/*--------PROCESSING CODE--------------------------------------------------------*/



echo "Connecting to MySQL...";
if ($debug) {
	$fh = fopen("iTunesDB.sql",'w');
	if (!$fh) die("Could not open debug output file for write.");
}
$db = new SimpleMySQLObject();
if (!$db->connect($DATABASE_ADDRESS,$DATABASE_LOGIN,$DATABASE_PASSWORD,$DATABASE_DBNAME)) die("STOP: Could not connect to MySQL server");

echo "...done\n";

echo "Loading iTunes library file...($ITUNES_LIBRARY_XML_PATH)";
$xmlDoc = new DOMDocument();
$xmlDoc->load($ITUNES_LIBRARY_XML_PATH);
echo "...done\n";

$root = $xmlDoc->documentElement->firstChild;

while ($root->nodeName == "#text") $root = $root->nextSibling;

//load root tree structure, this contains lib info, the songs database, and the playlists array
$docRootValues = parseValue($root,1);

//get itunes music folder location
$libraryRootFolder = $docRootValues['Music Folder'];
echo "Music folder root path: $libraryRootFolder\n";

echo "Cleaning up database...\n";
//drop old data tables.  I do this to ensure that the table is always set to the latest schema.
if ($db->tableExists($DATABASE_TABLE_PREFIX."tracks")) {
	$query = "DROP TABLE ".$DATABASE_TABLE_PREFIX."tracks;";
	if ($debug) {
		fwrite($fh, "$query\n");
		echo "   Dropped old tracks table.\n";
	} else {
		if ($db->query($query))
			echo "   Dropped old tracks table.\n";
		else die;
	}
}
if ($db->tableExists($DATABASE_TABLE_PREFIX."playlist_info")) {
	$query = "DROP TABLE ".$DATABASE_TABLE_PREFIX."playlist_info;";
	if ($debug) {
		fwrite($fh, "$query\n");
		echo "   Dropped old playlist info table.\n";
	} else {
		if ($db->query($query))
			echo "   Dropped old playlist info table.\n";
		else die;
	}
}
if ($db->tableExists($DATABASE_TABLE_PREFIX."playlist_content")) {
	$query = "DROP TABLE ".$DATABASE_TABLE_PREFIX."playlist_content;";
	if ($debug) {
		fwrite($fh, "$query\n");
		echo "   Dropped old playlist contents table.\n";
	} else {
		if ($db->query($query))
			echo "   Dropped old playlist contents table.\n";
		else die;
	}
}

//remake the new tables
echo "Creating data tables...";
$queryF = file_get_contents('table_schema.sql');
$queryF = str_replace('\t',' ', $queryF);
$queryF = str_replace('\n','', $queryF);
$queryF = str_replace('\r','', $queryF);
$queryF = str_replace('PREFIX',$DATABASE_TABLE_PREFIX, $queryF);
$queryF = explode(";",$queryF);
foreach($queryF as $query) {
	$query = trim($query);
	if ($query) {
		//print "$query\n";
		if ($debug) fwrite($fh, "$query\n");
		else if (!$db->query($query)) die();
	}
}
echo "...done\n";

//load the track list
$songsDict = parseValue($docRootValues['Tracks'],1);
$songCount = count($songsDict);
echo "Found $songCount tracks.  Adding to Database\n[";
$i=0;
foreach ($songsDict as $trackElement) {
	$track = parseValue($trackElement);
	
	$track['Location'] = str_replace($libraryRootFolder, '', $track['Location']); //trim file path to root of music folder
	
	//QUERY STRING: Title, Artist, Album, TrackID, FilePath, FileSize, Total Time, Rating, track number, track count, disc number, disc count, play count, date last played, date added
	
	$query = "INSERT INTO ".$DATABASE_TABLE_PREFIX."tracks VALUES (";
	$query.= '"'.mysql_real_escape_string($track['Name']).'", ';
	$query.= (isset($track['Artist']) ? '"'.mysql_real_escape_string($track['Artist']).'"' : 'NULL') .", ";
	$query.= (isset($track['Album']) ? '"'.mysql_real_escape_string($track['Album']).'"'  : 'NULL') .", ";
	$query.= $track['Track ID'] .", ";
	$query.= '"'.mysql_real_escape_string($track['Location']).'", ';
	$query.= (isset($track['Size']) ? $track['Size'] : 'NULL') .", ";
	$query.= $track['Total Time'] .", ";
	$query.= (isset($track['Rating']) ? $track['Rating'] : '0') .", ";
	$query.= (isset($track['Track Number']) ? $track['Track Number'] : 'NULL') .", ";
	$query.= (isset($track['Track Count']) ? $track['Track Count'] : 'NULL') .", ";
	$query.= (isset($track['Disc Number']) ? $track['Disc Number'] : 'NULL') .", ";
	$query.= (isset($track['Disc Count']) ? $track['Disc Count'] : 'NULL') .", ";
	$query.= (isset($track['Play Count']) ? $track['Play Count'] : 'NULL') .", ";
	$query.= (isset($track['Play Date UTC']) ? '"'.iTunesDateTOMySQLDate($track['Play Date UTC']).'"' : 'NULL') .", ";
	$query.= (isset($track['Date Added']) ? '"'.iTunesDateTOMySQLDate($track['Date Added']).'"' : 'NULL');
	$query.= ");";
	
	if ($debug) fwrite($fh, "$query\n");
	else if (!$db->query($query)) die();
	
	if (++$i%(floor($songCount/100)) == 0) echo '.'; //print a status dot every one percent
}
echo "]\nDone adding tracks.\n";


//load the playlists array
$playlistArray = parseValue($docRootValues['Playlists'],1);
echo "Found ", count($playlistArray), " potential playlist entries.  Adding to Database...\n";
for ($i=1;$i<count($playlistArray);$i++) { //item 0 is always the library.  We already have this loaded, don't need it again.
	$playlist = parseValue($playlistArray[$i]);
	
	if ($playlist['Distinguished Kind']) continue; //skip the generated categories like Movies, Podcasts, etc.
	if ($playlist['Folder']) continue; //skipping folders until I can figure out how I'll use them
	
	echo '   ',$playlist['Name'],'...';
	$query = "INSERT INTO ".$DATABASE_TABLE_PREFIX."playlist_info VALUES (".$playlist['Playlist ID'].', "'.mysql_real_escape_string($playlist['Name']).'", '.(isset($playlist['Smart Info'])?'true':'false').');';

	if ($debug) fwrite($fh, "$query\n");
	else if (!$db->query($query)) die();
	
	if ($playlist['Playlist Items']) {
		$query = "INSERT INTO ".$DATABASE_TABLE_PREFIX."playlist_content VALUES ";
		$i2 = 0;
		foreach ($playlist['Playlist Items'] as $trackid) {
			$query.= '('.$playlist['Playlist ID'].", ".$trackid['Track ID'].", $i2),";
			$i2++;
		}
		$query = substr($query,0,-1).';';
		
		if ($debug) fwrite($fh, "$query\n");
		elseif (!$db->query($query)) die();
	}
	echo "...done\n";
	
}

echo "Database update complete, closing connection...";

if ($debug) fclose($fh);
$db->disconnect();

echo "...done\n";
?>